package com.cpi.basilos.unit.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.log4j.Logger;

import com.cpi.basilos.unit.dto.UnitByOriginDTO;
import com.cpi.basilos.unit.dto.UnitDTO;
import com.cpi.basilos.unit.exception.UnitException;
import com.cs.common.exception.CSException;
import com.cs.common.pool.DBDAO;

public class UnitDAO extends DBDAO {
	private static final String SQL_GET_TRANSIT_BY_ORIGIN = "SELECT AL.codigo,SUM(UN.peso_origen) AS cantidad, COUNT(UN.peso_origen) AS tolvas, TG.nombre_corto,  EM.fecha, UN.embarque, TI.imagen FROM unidad AS UN, almacen AS AL, lote_unidad AS LU, lote AS LO, tipo_grano AS TG, embarque EM, transporte TR, trans_icono TI WHERE UN.status = 'Transito' AND UN.almacen_destino = ? AND AL.id_almacen = UN.almacen_origen AND LU.unidad = UN.id_unidad AND LO.id_lote = LU.lote AND TG.id_tipograno = LO.tipo_grano and EM.id_emb = UN.embarque and EM.transporte = TR.id_transporte and TR.icono = TI.id_icono GROUP BY AL.codigo, AL.nombre, TG.nombre_corto, EM.fecha, UN.embarque, TI.imagen";//$NON-NLS-1$
	private static final String SQL_GET_TRANSIT = "SELECT id_unidad, codigo_ao, peso_origen, fecha_emb, tipogra_lote, inicial, numero, sellos, humedad_origen, no_guia FROM vista_unidad WHERE almacen_destino = ? AND  status = 'Transito' ORDER BY fecha_emb ASC, id_unidad ASC";//$NON-NLS-1$
	private static org.apache.log4j.Logger logger = Logger.getLogger(UnitDAO.class);
	public List getTransitByOrigin(int warehouseId)throws UnitException{
		List unitByOriginDTOs = null;
		UnitByOriginDTO unitByOriginDTO = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Calendar date = null;
		try{
			unitByOriginDTOs = new ArrayList();
			preparedStatement = prepareSQL(SQL_GET_TRANSIT_BY_ORIGIN, dsCIMa);
			preparedStatement.setInt(1, warehouseId);
			resultSet = executeQuery(preparedStatement);
			while(resultSet.next()){
				date = Calendar.getInstance();
				unitByOriginDTO = new UnitByOriginDTO();
				unitByOriginDTO.setRoute(resultSet.getString(1));
				unitByOriginDTO.setAmount(resultSet.getDouble(2));
				unitByOriginDTO.setHoppers(resultSet.getInt(3));
				unitByOriginDTO.setCornType(resultSet.getString(4));
				date.setTimeInMillis(resultSet.getDate(5).getTime());
				unitByOriginDTO.setDate(date);
				unitByOriginDTO.setImage(resultSet.getString(7));
				unitByOriginDTOs.add(unitByOriginDTO);
			}
			connection = preparedStatement.getConnection();
		}catch(Exception exception){
			UnitException unitException = null;
			exception.printStackTrace(System.out);
			unitException = new UnitException(exception, CSException.LAYER_DAO, CSException.ACTION_LISTS);
			logger.error(exception);
			throw unitException;
		}finally{
			try{
				closeConnection(connection);
			}catch(Exception exception){}
		}
		return unitByOriginDTOs;
	}
	
	public List getTransit(int warehouseId)throws UnitException{
		List unitDTOs = null;
		UnitDTO unitDTO = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Calendar date = null;
		try{
			unitDTOs = new ArrayList();
			preparedStatement = prepareSQL(SQL_GET_TRANSIT, dsCIMa);
			preparedStatement.setInt(1, warehouseId);
			resultSet = executeQuery(preparedStatement);
			while(resultSet.next()){
				date = Calendar.getInstance();
				unitDTO = new UnitDTO();
				unitDTO.setId(resultSet.getInt(1));
				unitDTO.setRoute(resultSet.getString(2));
				unitDTO.setAmount(resultSet.getDouble(3));
				date.setTimeInMillis(resultSet.getDate(4).getTime());
				unitDTO.setDate(date);
				unitDTO.setCornTypeId(resultSet.getInt(5));
				unitDTO.setInitial(resultSet.getString(6));
				unitDTO.setNumber(resultSet.getString(7));
				unitDTO.setSeals(resultSet.getString(8));
				unitDTO.setOriginWetness(resultSet.getDouble(9));
				unitDTO.setGuide(resultSet.getString(10));
				unitDTOs.add(unitDTO);
			}
			connection = preparedStatement.getConnection();
		}catch(Exception exception){
			UnitException unitException = null;
			exception.printStackTrace(System.out);
			unitException = new UnitException(exception, CSException.LAYER_DAO, CSException.ACTION_LISTS);
			logger.error(exception);
			throw unitException;
		}finally{
			try{
				closeConnection(connection);
			}catch(Exception exception){}
		}
		return unitDTOs;
	}
}